/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package mongodbimport;

import java.net.UnknownHostException;
import java.util.Date;
import com.mongodb.BasicDBObject;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.DBCursor;
import com.mongodb.MongoClient;
import com.mongodb.MongoException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 *
 * @author THNghiep
 */
public class MongoDBImport {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
		try {

                        // ----------------------------------------------------------------------
			/**** Connect to MySQL ****/
                        Class.forName("com.mysql.jdbc.Driver").newInstance();
                        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/cspublicationcrawler", "root", "root");

                        String selectIdPaper = "SELECT idPaper from paper";
                        PreparedStatement pst1 = connection.prepareStatement(selectIdPaper, ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
                        ResultSet rs1 = pst1.executeQuery();

                        String selectPaperInfo = "SELECT "
                                + "p.idPaper, p.doi, p.isbn, p.url, p.title, p.abstractContent, p.volume, p.pages, p.year, p.viewPublication, p.bibTex, p.endNote, p.version, p.paperFile, "
                                + "a.authorName as authorNames, "
                                + "j.journalName, "
                                + "c.conferenceName, "
                                + "k.keyword as keywords, "
                                + "pp.idPaperRef as idPaperRefs "
                                + "FROM paper p LEFT JOIN author_paper ap ON p.idPaper = ap.idPaper "
                                + "LEFT JOIN author a ON a.idAuthor = ap.idAuthor "
                                + "LEFT JOIN conference c ON c.idConference = p.idConference "
                                + "LEFT JOIN journal j ON j.idJournal = p.idJournal "
                                + "LEFT JOIN paper_keyword pk ON pk.idPaper = p.idPaper "
                                + "LEFT JOIN keyword k ON k.idKeyword = pk.idKeyword "
                                + "LEFT JOIN paper_paper pp ON pp.idPaper = p.idPaper "
                                + "WHERE p.idPaper = ?";                        
                        PreparedStatement pst2 = connection.prepareStatement(selectPaperInfo, ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
                        // ----------------------------------------------------------------------

                        
                        // ----------------------------------------------------------------------
                        /**** Connect to MongoDB ****/
			// Since 2.10.0, uses MongoClient
			MongoClient mongo = new MongoClient("localhost", 27017);

			/**** Get database ****/
			// if database doesn't exists, MongoDB will create it for you
			DB db = mongo.getDB("CSPublication");

			/**** Get collection / table from 'testdb' ****/
			// if collection doesn't exists, MongoDB will create it for you
			DBCollection table = db.getCollection("paper_full");
                        // ----------------------------------------------------------------------


                        // ----------------------------------------------------------------------
                        // Read full info for each paper, then put it into MongoDB.
                        PaperFull paper = null;
                        while (rs1.next()) {
                            pst2.setString(1, rs1.getString(1));
                            ResultSet rs2 = pst2.executeQuery();
                            
                            if (rs2.next()) {
                                paper = new PaperFull();
                                
                                paper.setIdPaper(rs2.getString(1));
                                paper.setDoi(rs2.getString(2));
                                paper.setIsbn(rs2.getString(3));
                                paper.setUrl(rs2.getString(4));
                                paper.setTitle(rs2.getString(5));
                                paper.setAbstractContent(rs2.getString(6));
                                paper.setVolume(rs2.getString(7));
                                paper.setPages(rs2.getString(8));
                                paper.setYear(rs2.getString(9));
                                paper.setViewPublication(rs2.getString(10));
                                paper.setBibTex(rs2.getString(11));
                                paper.setEndNote(rs2.getString(12));
                                paper.setVersion(rs2.getString(13));
                                paper.setPaperFile(rs2.getString(14));
                                paper.setAuthorNames(rs2.getString(15));
                                paper.setJournalName(rs2.getString(16));
                                paper.setConferenceName(rs2.getString(17));
                                paper.setKeywords(rs2.getString(18));
                                paper.setIdPaperRefs(rs2.getString(19));
                            }

                            while (rs2.next()) {
                                paper.setAuthorNames(rs2.getString(15));
                                paper.setKeywords(rs2.getString(18));
                                paper.setIdPaperRefs(rs2.getString(19));
                            }

                            /**** Insert into MongoDB ****/
                            // create a document to store key and value
                            BasicDBObject document = new BasicDBObject();

                            document.put("idPaper", paper.getIdPaper());
                            document.put("doi", paper.getDoi());
                            document.put("isbn", paper.getIsbn());
                            document.put("url", paper.getUrl());
                            document.put("title", paper.getTitle());
                            document.put("abstractContent", paper.getAbstractContent());
                            document.put("volume", paper.getVolume());
                            document.put("pages", paper.getPages());
                            document.put("year", paper.getYear());
                            document.put("viewPublication", paper.getViewPublication());
                            document.put("bibTex", paper.getBibTex());
                            document.put("endNote", paper.getEndNote());
                            document.put("version", paper.getVersion());
                            document.put("paperFile", paper.getPaperFile());
                            document.put("authorNames", paper.getAuthorNames());
                            document.put("journalName", paper.getJournalName());
                            document.put("conferenceName", paper.getConferenceName());
                            document.put("keywords", paper.getKeywords());
                            document.put("idPaperRefs", paper.getIdPaperRefs());

                            table.insert(document);
                         }


		} catch (UnknownHostException e) {
			e.printStackTrace();
		} catch (MongoException e) {
			e.printStackTrace();
		} catch (Exception e) {
                        e.printStackTrace();
                }
    }
}
